﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    public class STRIP_SAMPLE_RULE
    {
        public static int pageNum = 20;

        #region field name 
        public static readonly string tableName = "STRIP_SAMPLE_RULE";
        public static readonly string fd_sampleName = "SAMPLE_NAME";
        public static readonly string fd_sampleNo = "SAMPLE_NO";
        public static readonly string fd_fltFrontLength = "FLT_FRONT_LENGTH";
        public static readonly string fd_mtFrontLength = "MT_FRONT_LENGTH";
        public static readonly string fd_atFrontLength = "AT_FRONT_LENGTH";
        public static readonly string fd_fltMiddleLength = "FLT_MIDDLE_LENGTH";
        public static readonly string fd_mtMiddleLength = "MT_MIDDLE_LENGTH";
        public static readonly string fd_atMiddleLength = "AT_MIDDLE_LENGTH";
        public static readonly string fd_fltEndLength = "FLT_END_LENGTH";
        public static readonly string fd_mtEndLength = "MT_END_LENGTH";
        public static readonly string fd_atEndLength = "AT_END_LENGTH";
        public static readonly string fd_updateTime = "UPDATE_TIME";

        #endregion

        #region 1116wp
        /// <summary>
        /// query stripsamplerule of the certain page
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <returns></returns>
        public static DataTable Select_StripSampleRule(int minRow, int maxRow)
        {
            try
            {
                string sqlstrip = string.Format("select * from (select a.*,ROWNUM rn from {0} a order by {1} desc) where rn>{2} and rn<={3}",
                    tableName, fd_updateTime, minRow, maxRow);

                DataTable striptable = OracleHelper.Exct_QueryTable(sqlstrip);
                if (striptable != null)
                {
                    return striptable;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
                throw (ex);
            }
        }
        /// <summary>
        /// insert stripsamplerule
        /// </summary>
        /// <param name="stripTable"></param>
        /// <returns></returns>
        public static DataTable Insert_StripSampleRule(DataTable stripTable)
        {
            try
            {
                if (ExcuteNewStripSampleRule(stripTable) > 0)
                {
                    return stripTable;
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }        
        private static int ExcuteNewStripSampleRule(DataTable dataTable)
        {
			try
			{
				string sql = string.Format("insert into {0} values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", tableName,
				  dataTable.Rows[0][fd_sampleName].ToString(), dataTable.Rows[0][fd_sampleNo].ToString(),
				  dataTable.Rows[0][fd_fltFrontLength].ToString(), dataTable.Rows[0][fd_mtFrontLength].ToString(), dataTable.Rows[0][fd_atFrontLength].ToString(),
				  dataTable.Rows[0][fd_fltMiddleLength].ToString(), dataTable.Rows[0][fd_mtMiddleLength].ToString(), dataTable.Rows[0][fd_atMiddleLength].ToString(),
				  dataTable.Rows[0][fd_fltEndLength].ToString(), dataTable.Rows[0][fd_mtEndLength].ToString(), dataTable.Rows[0][fd_atEndLength].ToString(),
				  dataTable.Rows[0][fd_updateTime].ToString());
				if (OracleHelper.Exct_NonQuery(sql) > 0)
				{
					LogHelper.WriteLog("ExcuteNewStripSampleRule Success");
					return 1;
				}
				else
				{
					return -1;
				}
			}
			catch (Exception ex)
			{
				LogHelper.WriteLog("ExcuteNewStripSampleRule", ex);
				throw ex;
			}
		}
        /// <summary>
        /// edit stripsamplerule information
        /// </summary>
        /// <param name="batchTable"></param>
        /// <returns></returns>
        public static DataTable Edit_StripSampleRule(DataTable batchTable)
        {
            try
            {
                if (ExcuteEditStripSampleRule(batchTable) > 0)
                {
                    return batchTable;
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }
        private static int ExcuteEditStripSampleRule(DataTable dataTable)
        {
			try
			{
				string sql = string.Format("UPDATE {0} SET {1}='{2}', {3}='{4}', {5}='{6}', {7}='{8}',{9}='{10}', {11}='{12}',{13}='{14}', {15}='{16}', {17}='{18}', {19}='{20}',{21}='{22}' WHERE {23}='{24}'",
									tableName, fd_sampleName, dataTable.Rows[0][fd_sampleName].ToString(), fd_fltFrontLength, dataTable.Rows[0][fd_fltFrontLength].ToString(), fd_mtFrontLength, dataTable.Rows[0][fd_mtFrontLength].ToString(), fd_atFrontLength, dataTable.Rows[0][fd_atFrontLength].ToString(),
									fd_fltMiddleLength, dataTable.Rows[0][fd_fltMiddleLength].ToString(), fd_mtMiddleLength, dataTable.Rows[0][fd_mtMiddleLength].ToString(), fd_atMiddleLength, dataTable.Rows[0][fd_atMiddleLength].ToString(), 
									fd_fltEndLength, dataTable.Rows[0][fd_fltEndLength].ToString(), fd_mtEndLength, dataTable.Rows[0][fd_mtEndLength].ToString(), fd_atEndLength, dataTable.Rows[0][fd_atEndLength].ToString(),
									fd_updateTime, dataTable.Rows[0][fd_updateTime].ToString(), fd_sampleNo, dataTable.Rows[0][fd_sampleNo].ToString()
									);
				if (OracleHelper.Exct_NonQuery(sql) > 0)
				{
					LogHelper.WriteLog("ExcuteEditStripSampleRule Success");
					return 1;
				}
				else
				{
					return -1;
				}
			}
			catch (Exception ex)
			{
				LogHelper.WriteLog("ExcuteEditStripSampleRule", ex);
				throw ex;
			}
		}
        /// <summary>
        /// delete stripsamplerule information
        /// </summary>
        /// <param name="sampleNo"></param>
        /// <returns></returns>
        public static DataTable Delete_StripSampleRule(string sampleNo)
        {
            try
            {
                string deleteBatchSql = string.Format("delete from {0} where {1}='{2}'", tableName, fd_sampleNo, sampleNo);
                if (OracleHelper.Exct_NonQuery(deleteBatchSql) > 0)
                {
                    return Select_StripSampleRule(0, 10);
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

        #endregion        

        #region 1204wp
        /// <summary>
        /// query stripsamplerule of the certain page
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="stripSampleRuleTotalPages"></param>
        /// <returns></returns>
        public static DataTable Select_StripSampleRuleByIndex(int minRow, int maxRow, out string stripSampleRuleTotalPages)
        {
            try
            {
                string sqlStrip = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} order by {1} desc) a) where rn>{2} and rn<={3}",
                 tableName, fd_updateTime, minRow, maxRow);
                string sqlstripcount = string.Format("select count(*) from {0}",
                  tableName);
                DataTable striptable = OracleHelper.Exct_QueryTable(sqlStrip);
                if (striptable != null)
                {
                    int stripPages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlstripcount));
                    if (stripPages % pageNum == 0)
                    {
                        stripSampleRuleTotalPages = (stripPages / pageNum).ToString();
                    }
                    else
                    {
                        stripSampleRuleTotalPages = (stripPages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("Select_StripSampleRuleByIndex Success");
                    return striptable;
                }
                else
                {
                    stripSampleRuleTotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                stripSampleRuleTotalPages = "";
                LogHelper.WriteLog("Select_StripSampleRuleByIndex", ex);
                throw ex;
            }
        }
        #endregion

    }
}
